# backend/app/models/models.py
"""
自动化测试系统数据模型
==================

⚠️ 重要说明：
本文件定义的是 **自动化测试系统** 的数据模型，与功能测试系统完全独立！

数据库表名对比：
- 自动化测试：auto_test_cases, test_suites, modules, base_steps, coordinates 等
- 功能测试：test_cases, test_libraries, test_modules, test_plans, test_iterations 等

两个系统使用不同的表名和数据模型，确保数据不会互相影响。
- 自动化测试模型：app/model.py (本文件)
- 功能测试模型：app/models/test_models.py

请勿与功能测试系统的表产生混淆！
"""
from datetime import datetime
from enum import Enum as PyEnum
from flask import current_app
from sqlalchemy import Enum

from .utils.database import db


class Device(db.Model):
    __tablename__ = 'devices'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    strategies = db.relationship('Strategy', back_populates='device', cascade='all, delete-orphan')

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'name': self.name,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S'),
            'strategies': [strategy.to_dict() for strategy in self.strategies]
        }

    @staticmethod
    def create_device(data):
        """创建新设备"""
        try:
            new_device = Device(name=data['name'])
            db.session.add(new_device)
            db.session.commit()
            return new_device
        except Exception as e:
            db.session.rollback()
            raise e

    def update_device(self, data):
        """更新设备信息"""
        try:
            if 'name' in data:
                self.name = data['name']
            db.session.commit()
            return self
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def delete_device(device_id):
        """删除设备"""
        try:
            device = Device.query.get(device_id)
            if device:
                db.session.delete(device)
                db.session.commit()
                return True
            return False
        except Exception as e:
            db.session.rollback()# python-developer 中文规则文件
            raise e

    @staticmethod
    def get_device_by_id(device_id):
        """根据ID获取设备"""
        return Device.query.get(device_id)

    @staticmethod
    def get_all_devices():
        """获取所有设备"""
        return Device.query.all()


class Strategy(db.Model):
    __tablename__ = 'strategies'

    id = db.Column(db.Integer, primary_key=True)
    device_id = db.Column(db.Integer, db.ForeignKey('devices.id'))
    weekdays = db.Column(db.String(100), nullable=False)
    test_case = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    device = db.relationship('Device', back_populates='strategies')

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'device_id': self.device_id,
            'weekdays': self.weekdays,
            'test_case': self.test_case,
            'description': self.description,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

    @staticmethod
    def create_strategy(data):
        """创建新策略"""
        try:
            new_strategy = Strategy(
                device_id=data['device_id'],
                weekdays=data['weekdays'],
                test_case=data['test_case'],
                description=data.get('description')
            )
            db.session.add(new_strategy)
            db.session.commit()
            return new_strategy
        except Exception as e:
            db.session.rollback()
            raise e

    def update_strategy(self, data):
        """更新策略信息"""
        try:
            for key, value in data.items():
                if hasattr(self, key) and key not in ['id', 'created_at']:
                    setattr(self, key, value)
            db.session.commit()
            return self
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def delete_strategy(strategy_id):
        """删除策略"""
        try:
            strategy = Strategy.query.get(strategy_id)
            if strategy:
                db.session.delete(strategy)
                db.session.commit()
                return True
            return False
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def get_strategy_by_id(strategy_id):
        """根据ID获取策略"""
        return Strategy.query.get(strategy_id)

    @staticmethod
    def get_strategies_by_device(device_id):
        """获取设备的所有策略"""
        return Strategy.query.filter_by(device_id=device_id).all()


class Module(db.Model):
    """功能模块表"""
    __tablename__ = 'modules'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False, unique=True, comment='模块名称')
    directory_name = db.Column(db.String(100), nullable=True, comment='目录名称')
    description = db.Column(db.Text, nullable=True, comment='模块描述')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关联关系定义
    coordinates = db.relationship('Coordinate', backref='module', lazy=True,
                                  cascade='all, delete-orphan')
    base_steps = db.relationship('BaseStep', backref='module', lazy=True,
                                 cascade='all, delete-orphan')
    test_cases = db.relationship('TestCase', backref='module', lazy=True,
                                 cascade='all, delete-orphan')

    def __repr__(self):
        return f'<Module {self.name}>'

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'name': self.name,
            'directory_name': self.directory_name,
            'description': self.description,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

    @staticmethod
    def create_module(data):
        """创建新模块"""
        try:
            new_module = Module(
                name=data['name'],
                directory_name=data.get('directory_name'),
                description=data.get('description')
            )
            db.session.add(new_module)
            db.session.commit()
            return new_module
        except Exception as e:
            db.session.rollback()
            raise e

    def update_module(self, data):
        """更新模块信息"""
        try:
            for key, value in data.items():
                if hasattr(self, key) and key != 'id':
                    setattr(self, key, value)
            db.session.commit()
            return self
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def delete_module(module_id):
        """删除模块"""
        try:
            module = Module.query.get(module_id)
            if module:
                db.session.delete(module)
                db.session.commit()
                return True
            return False
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def get_module_by_id(module_id):
        """根据ID获取模块"""
        return Module.query.get(module_id)

    @staticmethod
    def get_all_modules():
        """获取所有模块"""
        return Module.query.all()


class Coordinate(db.Model):
    """控件坐标表"""
    __tablename__ = 'coordinates'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    element_name = db.Column(db.String(100), nullable=False, comment='控件名称')
    module_id = db.Column(db.Integer, db.ForeignKey('modules.id', ondelete='CASCADE'),
                          nullable=False, comment='所属模块ID')
    description = db.Column(db.Text, nullable=True, comment='控件描述')
    x_coord = db.Column(db.Integer, nullable=False, comment='X坐标')
    y_coord = db.Column(db.Integer, nullable=False, comment='Y坐标')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def __repr__(self):
        return f'<Coordinate {self.element_name} ({self.x_coord},{self.y_coord})>'

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'element_name': self.element_name,
            'module_id': self.module_id,
            'description': self.description,
            'x_coord': self.x_coord,
            'y_coord': self.y_coord,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

    @staticmethod
    def create_coordinate(data):
        """创建新的坐标记录"""
        try:
            new_coordinate = Coordinate(
                element_name=data['element_name'],
                module_id=data['module_id'],
                description=data.get('description'),  # 可选字段
                x_coord=data['x_coord'],
                y_coord=data['y_coord']
            )
            db.session.add(new_coordinate)
            db.session.commit()
            return new_coordinate
        except Exception as e:
            db.session.rollback()
            raise e

    def update_coordinate(self, data):
        """更新坐标记录"""
        try:
            for key, value in data.items():
                if hasattr(self, key) and key != 'id':
                    setattr(self, key, value)
            db.session.commit()
            return self
        except Exception as e:
            db.session.rollback()
            raise e


# 步骤类型枚举
class StepType(PyEnum):
    """步骤类型枚举"""
    LEFT_CLICK = '移动并点击'
    LEFT_HOLD = '长按左键'
    LEFT_RELEASE = '释放左键'
    CLICK = '点击左键'
    RIGHT_CLICK = '移动并点击右键'
    RIGHT_HOLD = '长按右键'
    CLICK_RIGHT = '点击右键'
    RIGHT_RELEASE = '释放右键'
    MOVE_TO = '移动至'
    BOTH_HOLD = '长按左右键'
    CUSTOM = "custom"      
    NORMAL = "normal"      

    @classmethod
    def get_values(cls):
        """获取所有步类型值"""
        return [e.value for e in cls]


class CustomStepType(PyEnum):
    """自定义步骤类型枚举"""
    SLEEP = "sleep"
    FOR_LOOP = "for"
    ASSERT = "assert"
    CUSTOM_METHOD = "custom-method"
    LEFT_CLICK = "点击左键"
    RIGHT_CLICK = "点击右键"
    LEFT_DOWN = "按住左键"
    LEFT_UP = "松开左键" 
    RIGHT_DOWN = "按住右键"
    RIGHT_UP = "松开右键"

    @classmethod
    def get_values(cls):
        """获取所有步骤类型值"""
        return [e.value for e in cls]


class CustomStep(db.Model):
    """自定义步骤表"""
    __tablename__ = 'custom_steps'
    
    id = db.Column(db.Integer, primary_key=True)
    step_name = db.Column(db.String(100), nullable=False)
    step_type = db.Column(db.String(50), nullable=False)  # 使用 CustomStepType 中的值
    description = db.Column(db.String(500))
    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now)


class BaseStep(db.Model):
    """基础步骤表"""
    __tablename__ = 'base_steps'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    module_id = db.Column(db.Integer, db.ForeignKey('modules.id', ondelete='CASCADE'),
                          nullable=False, comment='所属模块ID')
    step_name = db.Column(db.String(100), nullable=False, comment='步骤名称')
    step_type = db.Column(
        db.Enum(*StepType.get_values(), name='step_type_enum'),
        nullable=False,
        comment='步骤类型'
    )
    description = db.Column(db.Text, nullable=True, comment='步骤描述')
    coordinate_id = db.Column(db.Integer, db.ForeignKey('coordinates.id', ondelete='SET NULL'),
                              nullable=True, comment='关联坐标ID')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 建立与坐标表的关联关系
    coordinate = db.relationship('Coordinate', backref=db.backref('base_steps', lazy=True))

    def __repr__(self):
        return f'<BaseStep {self.id} - Type: {self.step_type.value}>'

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'module_id': self.module_id,
            'step_name': self.step_name,
            'step_type': self.step_type if isinstance(self.step_type, str) else self.step_type.value,
            'description': self.description,
            'coordinate_id': self.coordinate_id,
            'coordinate': self.coordinate.to_dict() if self.coordinate else None,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

    @staticmethod
    def create_base_step(data):
        """创建新的基础步骤"""
        try:
            # 验证步骤类型是否有效
            step_type = data['step_type']
            valid_types = [e.value for e in StepType]
            if step_type not in valid_types:
                raise ValueError(
                    f"无效的步骤类型: {step_type}. "
                    f"有效的类型包括: {', '.join(valid_types)}"
                )

            new_step = BaseStep(
                module_id=data['module_id'],
                step_name=data['step_name'],
                step_type=step_type,
                description=data.get('description'),
                coordinate_id=data.get('coordinate_id')
            )
            db.session.add(new_step)
            db.session.commit()
            return new_step
        except Exception as e:
            db.session.rollback()
            current_app.logger.error(
                f"创建基础步骤失败: {str(e)}\n"
                f"请求数据: {data}"
            )
            raise e

    def update_base_step(self, data):
        """更新基础步骤"""
        try:
            if 'step_type' in data:
                # 确保 step_type 是字符串且为小写
                step_type = data['step_type'].lower()
                # 验证是否为有效的步骤类型
                if step_type not in StepType.get_values():
                    raise ValueError(
                        f"无效的步骤类型: {step_type}. "
                        f"有效的类型包括: {', '.join(StepType.get_values())}"
                    )
                # 直接使用字符串值而不是枚举对象
                self.step_type = step_type
                # 从数据中移除 step_type
                del data['step_type']

            # 更新其他字段
            for key, value in data.items():
                if hasattr(self, key) and key != 'id':
                    setattr(self, key, value)

            db.session.commit()
            return self
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def get_steps_by_module(module_id):
        """获取指定模块的所有基础步骤"""
        result = db.session.query(BaseStep) \
            .join(Module, Module.id == BaseStep.module_id) \
            .filter(Module.id == module_id) \
            .all()
        return result
    @staticmethod
    def get_step_types():
        """获取所有可用的步骤类型"""
        return [{'label': step_type.value, 'value': step_type.value} for step_type in StepType]


# 首先定义关联表
test_case_pre_steps = db.Table(
    'auto_test_case_pre_steps',
    db.Column('case_id', db.Integer, 
              db.ForeignKey('auto_test_cases.id', ondelete='CASCADE')),
    db.Column('step_id', db.Integer, 
              db.ForeignKey('base_steps.id', ondelete='CASCADE')),
    db.Column('step_order', db.Integer, comment='步骤执行顺序'),
    db.UniqueConstraint('case_id', 'step_order', name='unique_case_step_order')
)

# 测试套件前置步骤关联表
test_suite_pre_steps = db.Table(
    'test_suite_pre_steps',
    db.Column('suite_id', db.Integer, db.ForeignKey('test_suites.id', ondelete='CASCADE')),
    db.Column('step_id', db.Integer, nullable=False),  # 移除外键约束
    db.Column('is_custom', db.Boolean, default=False, comment='是否为自定义步骤'),
    db.Column('step_order', db.Integer, comment='步骤顺序')
)

# 测试套件用例前置步骤关联表
test_suite_case_pre_steps = db.Table(
    'test_suite_case_pre_steps',
    db.Column('suite_id', db.Integer, db.ForeignKey('test_suites.id', ondelete='CASCADE')),
    db.Column('step_id', db.Integer, nullable=False),  # 移除外键约束
    db.Column('is_custom', db.Boolean, default=False, comment='是否为自定义步骤'),
    db.Column('step_order', db.Integer, comment='步骤顺序')
)

class TestCase(db.Model):
    """测试用例表"""
    __tablename__ = 'auto_test_cases'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    case_name = db.Column(db.String(200), nullable=False, comment='用例名称')
    module_id = db.Column(db.Integer, db.ForeignKey('modules.id', ondelete='CASCADE'),
                          nullable=False, comment='所属模块ID')
    description = db.Column(db.Text, nullable=True, comment='用例描述')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关联步骤
    steps = db.relationship('TestCaseStep', backref='test_case',
                            order_by='TestCaseStep.step_order',
                            cascade='all, delete-orphan')
    
    # 关联前置步骤
    pre_steps = db.relationship(
        'BaseStep',
        secondary=test_case_pre_steps,
        order_by=test_case_pre_steps.c.step_order,
        backref=db.backref('test_cases', lazy='dynamic')
    )

    def __repr__(self):
        return f'<TestCase {self.case_name}>'

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'case_name': self.case_name,
            'module_id': self.module_id,
            'description': self.description,
            'pre_steps': [{
                'id': step.id,
                'name': step.step_name,
                'module': step.module.name,
                'description': step.description,
                'type': step.step_type,
                'coordinate': step.coordinate.to_dict() if step.coordinate else None
            } for step in self.pre_steps],
            'steps': [step.to_dict() for step in self.steps],
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

    @staticmethod
    def create_test_case(data):
        """创建新的测试用例"""
        try:
            new_case = TestCase(
                case_name=data['case_name'],
                module_id=data['module_id'],
                description=data.get('description')
            )
            db.session.add(new_case)
            db.session.flush()  # 获取new_case.id

            # 创建步骤
            if 'steps' in data:
                for index, step_data in enumerate(data['steps']):
                    TestCaseStep.create_step(new_case.id, step_data, index)

            db.session.commit()
            return new_case
        except Exception as e:
            db.session.rollback()
            raise e

    def update_test_case(self, data):
        """更新测试用例"""
        try:
            # 更新基本信息
            self.case_name = data.get('case_name', self.case_name)
            self.description = data.get('description', self.description)

            # 如果提供了steps数据，更新步骤
            if 'steps' in data:
                # 删除现有步骤
                TestCaseStep.query.filter_by(test_case_id=self.id).delete()

                # 创建新的步骤
                for index, step_data in enumerate(data['steps']):
                    TestCaseStep.create_step(self.id, step_data, index)

            db.session.commit()
            return self
        except Exception as e:
            db.session.rollback()
            raise e


class TestCaseStep(db.Model):
    """测试用例步骤表"""
    __tablename__ = 'auto_test_case_steps'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    test_case_id = db.Column(db.Integer, db.ForeignKey('auto_test_cases.id', ondelete='CASCADE'),
                             nullable=False, comment='测试用例ID')
    step_id = db.Column(db.Integer, db.ForeignKey('base_steps.id'),
                        nullable=True, comment='基础步骤ID')
    custom_step_id = db.Column(db.Integer, db.ForeignKey('custom_steps.id'),
                        nullable=True, comment='自定义步骤ID')
    step_order = db.Column(db.Integer, nullable=False, comment='步骤顺序')
    parameters = db.Column(db.String(500), nullable=True, comment='步骤参数')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关联基础步骤
    base_step = db.relationship('BaseStep')
    custom_step = db.relationship('CustomStep')

    def __repr__(self):
        return f'<TestCaseStep {self.id} - Order: {self.step_order}>'

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'test_case_id': self.test_case_id,
            'step_id': self.step_id,
            'custom_step_id': self.custom_step_id,
            'step_order': self.step_order,
            'parameters': self.parameters,
            'base_step': self.base_step.to_dict() if self.base_step else None,
            'custom_step': {
                'id': self.custom_step.id,
                'step_name': self.custom_step.step_name,
                'step_type': self.custom_step.step_type,
                'description': self.custom_step.description
            } if self.custom_step else None,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

    @staticmethod
    def create_step(test_case_id, step_data, order):
        """创建测试用例步骤"""
        try:
            new_step = TestCaseStep(
                test_case_id=test_case_id,
                step_id=step_data.get('step_id'),
                custom_step_id=step_data.get('custom_step_id'),
                step_order=order,
                parameters=step_data.get('parameters')
            )
            db.session.add(new_step)
            return new_step
        except Exception as e:
            raise e

    @staticmethod
    def reorder_steps(test_case_id, step_orders):
        """重新排序测试用例步骤"""
        try:
            for step_id, new_order in step_orders.items():
                step = TestCaseStep.query.get(step_id)
                if step and step.test_case_id == test_case_id:
                    step.step_order = new_order
            db.session.commit()
        except Exception as e:
            db.session.rollback()
            raise e


class ExecutionStatus(str, PyEnum):
    """执行状态枚举"""
    SUCCESS = 'SUCCESS'
    FAILED = 'FAILED'
    RUNNING = 'RUNNING'
    PENDING = 'PENDING'


# 用例集合与测试用例的关联表
test_suite_cases = db.Table(
    'auto_test_suite_cases',
    db.Column('suite_id', db.Integer, db.ForeignKey('test_suites.id', ondelete='CASCADE')),
    db.Column('case_id', db.Integer, db.ForeignKey('auto_test_cases.id', ondelete='CASCADE')),
    db.Column('case_order', db.Integer, comment='用例执行顺序'),
    db.UniqueConstraint('suite_id', 'case_order', name='unique_case_order')
)

class TestSuite(db.Model):
    """测试用例集合表"""
    __tablename__ = 'test_suites'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    suite_name = db.Column(db.String(200), nullable=False, comment='集合名称')
    run_times = db.Column(db.Integer, nullable=False, default=1, comment='执行次数')
    description = db.Column(db.Text, nullable=True, comment='测试描述')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, 
                          onupdate=datetime.utcnow)

    # 关联测试用例
    cases = db.relationship(
        'TestCase',
        secondary=test_suite_cases,
        order_by=test_suite_cases.c.case_order,
        backref=db.backref('suites', lazy='dynamic')
    )

    # 套件前置步骤 - 基础步骤
    suite_pre_steps = db.relationship(
        'BaseStep',
        secondary=test_suite_pre_steps,
        primaryjoin="and_(TestSuite.id==test_suite_pre_steps.c.suite_id, "
                   "test_suite_pre_steps.c.is_custom==False)",
        secondaryjoin="BaseStep.id==test_suite_pre_steps.c.step_id",
        order_by=test_suite_pre_steps.c.step_order,
        backref=db.backref('suite_pre_suites', lazy='dynamic')
    )

    # 套件前置步骤 - 自定义步骤
    suite_pre_custom_steps = db.relationship(
        'CustomStep',
        secondary=test_suite_pre_steps,
        primaryjoin="and_(TestSuite.id==test_suite_pre_steps.c.suite_id, "
                   "test_suite_pre_steps.c.is_custom==True)",
        secondaryjoin="CustomStep.id==test_suite_pre_steps.c.step_id",
        order_by=test_suite_pre_steps.c.step_order,
        backref=db.backref('suite_pre_custom_suites', lazy='dynamic')
    )

    # 用例前置步骤 - 基础步骤
    case_pre_steps = db.relationship(
        'BaseStep',
        secondary=test_suite_case_pre_steps,
        primaryjoin="and_(TestSuite.id==test_suite_case_pre_steps.c.suite_id, "
                   "test_suite_case_pre_steps.c.is_custom==False)",
        secondaryjoin="BaseStep.id==test_suite_case_pre_steps.c.step_id",
        order_by=test_suite_case_pre_steps.c.step_order,
        backref=db.backref('case_pre_suites', lazy='dynamic')
    )

    # 用例前置步骤 - 自定义步骤
    case_pre_custom_steps = db.relationship(
        'CustomStep',
        secondary=test_suite_case_pre_steps,
        primaryjoin="and_(TestSuite.id==test_suite_case_pre_steps.c.suite_id, "
                   "test_suite_case_pre_steps.c.is_custom==True)",
        secondaryjoin="CustomStep.id==test_suite_case_pre_steps.c.step_id",
        order_by=test_suite_case_pre_steps.c.step_order,
        backref=db.backref('case_pre_custom_suites', lazy='dynamic')
    )

    def to_dict(self):
        """转换为字典格式"""
        # 获取套件前置步骤
        suite_pre_steps = []
        
        # 直接从关联表获取数据，保持原始顺序
        stmt = test_suite_pre_steps.select().where(
            test_suite_pre_steps.c.suite_id == self.id
        ).order_by(test_suite_pre_steps.c.step_order)
        
        pre_steps = db.session.execute(stmt).fetchall()
        
        for step in pre_steps:
            if step.is_custom:
                # 获取自定义步骤
                custom_step = CustomStep.query.get(step.step_id)
                if custom_step:
                    suite_pre_steps.append({
                        'id': custom_step.id,
                        'name': custom_step.step_name,
                        'type': 'custom',
                        'module': '自定义步骤',
                        'description': custom_step.description,
                        'is_custom': True
                    })
            else:
                # 获取基础步骤
                base_step = BaseStep.query.get(step.step_id)
                if base_step:
                    suite_pre_steps.append({
                        'id': base_step.id,
                        'name': base_step.step_name,
                        'type': base_step.step_type,
                        'module': base_step.module.name,
                        'description': base_step.description,
                        'coordinate': base_step.coordinate.to_dict() if base_step.coordinate else None,
                        'is_custom': False
                    })

        # 用例前置步骤使用相同的逻辑
        case_pre_steps = []
        stmt = test_suite_case_pre_steps.select().where(
            test_suite_case_pre_steps.c.suite_id == self.id
        ).order_by(test_suite_case_pre_steps.c.step_order)
        
        pre_steps = db.session.execute(stmt).fetchall()
        
        for step in pre_steps:
            if step.is_custom:
                custom_step = CustomStep.query.get(step.step_id)
                if custom_step:
                    case_pre_steps.append({
                        'id': custom_step.id,
                        'name': custom_step.step_name,
                        'type': 'custom',
                        'module': '自定义步骤',
                        'description': custom_step.description,
                        'is_custom': True
                    })
            else:
                base_step = BaseStep.query.get(step.step_id)
                if base_step:
                    case_pre_steps.append({
                        'id': base_step.id,
                        'name': base_step.step_name,
                        'type': base_step.step_type,
                        'module': base_step.module.name,
                        'description': base_step.description,
                        'coordinate': base_step.coordinate.to_dict() if base_step.coordinate else None,
                        'is_custom': False
                    })

        # 手动获取测试集合中的用例 - 从关联表查询自动化测试用例
        cases = []
        stmt = test_suite_cases.select().where(
            test_suite_cases.c.suite_id == self.id
        ).order_by(test_suite_cases.c.case_order)
        
        suite_cases = db.session.execute(stmt).fetchall()
        
        for suite_case in suite_cases:
            # 从auto_test_cases表获取用例
            test_case = TestCase.query.get(suite_case.case_id)
            if test_case:
                cases.append({
                    'id': test_case.id,
                    'case_name': test_case.case_name,
                    'module_name': test_case.module.name if test_case.module else None,
                    'description': test_case.description
                })

        return {
            'id': self.id,
            'suite_name': self.suite_name,
            'run_times': self.run_times,
            'description': self.description,
            'suite_pre_steps': suite_pre_steps,  # 直接使用从关联表获取的数据
            'case_pre_steps': case_pre_steps,    # 直接使用从关联表获取的数据
            'cases': cases,  # 使用手动查询的自动化测试用例数据
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

    @staticmethod
    def create_suite(data):
        """创建测试用例集合"""
        try:
            new_suite = TestSuite(
                suite_name=data['suite_name'],
                description=data.get('description')
            )
            db.session.add(new_suite)
            db.session.flush()

            # 添加套件前置步骤关联
            if 'suite_pre_steps' in data and data['suite_pre_steps']:
                for index, step_data in enumerate(data['suite_pre_steps']):
                    stmt = test_suite_pre_steps.insert().values(
                        suite_id=new_suite.id,
                        step_id=step_data['id'],
                        step_order=index
                    )
                    db.session.execute(stmt)

            # 添加用例前置步骤关联
            if 'case_pre_steps' in data and data['case_pre_steps']:
                for index, step_data in enumerate(data['case_pre_steps']):
                    stmt = test_suite_case_pre_steps.insert().values(
                        suite_id=new_suite.id,
                        step_id=step_data['id'],
                        step_order=index
                    )
                    db.session.execute(stmt)

            # 添加用例关联
            if 'cases' in data and data['cases']:
                for index, case_data in enumerate(data['cases']):
                    stmt = test_suite_cases.insert().values(
                        suite_id=new_suite.id,
                        case_id=case_data['id'],
                        case_order=index
                    )
                    db.session.execute(stmt)

            db.session.commit()
            return new_suite
        except Exception as e:
            db.session.rollback()
            current_app.logger.error(
                f"创建测试集合失败: {str(e)}\n"
                f"请求数据: {data}"
            )
            raise ValueError(f"创建失败: {str(e)}")

    def update_suite(self, data):
        """更新测试集合"""
        try:
            if 'suite_name' in data:
                self.suite_name = data['suite_name']
            if 'description' in data:
                self.description = data['description']

            # 更新套件前置步骤
            if 'suite_pre_steps' in data:
                db.session.execute(
                    test_suite_pre_steps.delete().where(
                        test_suite_pre_steps.c.suite_id == self.id
                    )
                )
                for index, step_data in enumerate(data['suite_pre_steps']):
                    stmt = test_suite_pre_steps.insert().values(
                        suite_id=self.id,
                        step_id=step_data['id'],
                        step_order=index
                    )
                    db.session.execute(stmt)

            # 更新用例前置步骤
            if 'case_pre_steps' in data:
                db.session.execute(
                    test_suite_case_pre_steps.delete().where(
                        test_suite_case_pre_steps.c.suite_id == self.id
                    )
                )
                for index, step_data in enumerate(data['case_pre_steps']):
                    stmt = test_suite_case_pre_steps.insert().values(
                        suite_id=self.id,
                        step_id=step_data['id'],
                        step_order=index
                    )
                    db.session.execute(stmt)

            # 更新用例关联
            if 'cases' in data:
                db.session.execute(
                    test_suite_cases.delete().where(
                        test_suite_cases.c.suite_id == self.id
                    )
                )
                for index, case_data in enumerate(data['cases']):
                    stmt = test_suite_cases.insert().values(
                        suite_id=self.id,
                        case_id=case_data['id'],
                        case_order=index
                    )
                    db.session.execute(stmt)

            db.session.commit()
            return self
        except Exception as e:
            db.session.rollback()
            raise e

    @classmethod
    def delete_suite(cls, suite_id):
        """删除测试集合"""
        try:
            suite = cls.query.get(suite_id)
            if not suite:
                return False
            
            # 先清空关联关系
            db.session.execute(
                test_suite_pre_steps.delete().where(
                    test_suite_pre_steps.c.suite_id == suite_id
                )
            )
            db.session.execute(
                test_suite_case_pre_steps.delete().where(
                    test_suite_case_pre_steps.c.suite_id == suite_id
                )
            )
            
            # 删除测试套件本身
            db.session.delete(suite)
            db.session.commit()
            return True
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def get_suite_by_id(suite_id):
        """根据ID获取测试集合"""
        return TestSuite.query.get(suite_id)

    @staticmethod
    def get_all_suites():
        """获取所有测试集合"""
        return TestSuite.query.all()

# 设备型号表
class DeviceModel(db.Model):
    """设备型号表"""
    __tablename__ = 'device_models'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    model_name = db.Column(db.String(50), nullable=False, unique=True, comment='型号名称')
    host = db.Column(db.String(100), nullable=False, comment='设备IP')
    port = db.Column(db.Integer, nullable=False, comment='端口号')
    username = db.Column(db.String(50), nullable=False, comment='用户名')
    password = db.Column(db.String(50), nullable=False, comment='密码')
    timeout = db.Column(db.Integer, nullable=False, default=10, comment='超时时间')
    mac_address = db.Column(db.String(50), nullable=False, comment='MAC地址')
    vuid = db.Column(db.String(50), nullable=False, comment='设备唯一标识')
    node = db.Column(db.String(50), nullable=False, default='KAOJI1', comment='Jenkins节点名称')
    description = db.Column(db.Text, nullable=True, comment='设备描述')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关联策略
    strategy = db.relationship('TestStrategy', backref='device_model', uselist=False)

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'model_name': self.model_name,
            'host': self.host,
            'port': self.port,
            'username': self.username,
            'password': self.password,
            'timeout': self.timeout,
            'mac_address': self.mac_address,
            'vuid': self.vuid,
            'node': self.node,
            'description': self.description,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

# 测试策略表
class TestStrategy(db.Model):
    """测试策略表"""
    __tablename__ = 'test_strategies'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    device_model_id = db.Column(db.Integer, db.ForeignKey('device_models.id', ondelete='CASCADE'),
                               nullable=False, comment='设备型号ID')
    name = db.Column(db.String(100), nullable=False, comment='策略名称')
    description = db.Column(db.Text, nullable=True, comment='策略描述')
    is_traverse_resolution = db.Column(db.Boolean, default=False, comment='是否遍历分辨率')
    is_active = db.Column(db.Boolean, nullable=False, default=False, comment='是否启用')
    is_monitoring = db.Column(db.Boolean, nullable=False, default=False, comment='是否正在监控')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关联每日执行计划
    daily_plans = db.relationship('DailyExecutionPlan', backref='strategy',
                                cascade='all, delete-orphan')

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'device_model_id': self.device_model_id,
            'name': self.name,
            'description': self.description,
            'is_traverse_resolution': self.is_traverse_resolution,
            'is_active': self.is_active,
            'is_monitoring': self.is_monitoring,
            'daily_plans': [plan.to_dict() for plan in self.daily_plans],
            'device_model': self.device_model.to_dict(),
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

# 每日执行计划表
class DailyExecutionPlan(db.Model):
    """每日执行计划"""
    __tablename__ = 'daily_execution_plans'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    strategy_id = db.Column(db.Integer, db.ForeignKey('test_strategies.id', ondelete='CASCADE'),
                          nullable=False, comment='策略ID')
    weekday = db.Column(db.Integer, nullable=False, comment='星期几(0-6)')
    execution_time = db.Column(db.Time, nullable=False, comment='执行时间')
    test_suite_id = db.Column(db.Integer, db.ForeignKey('test_suites.id', ondelete='SET NULL'),
                             nullable=True, comment='测试套件ID')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    # 添加与测试套件的关联
    test_suite = db.relationship('TestSuite', backref=db.backref('daily_plans', lazy=True))

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'strategy_id': self.strategy_id,
            'weekday': self.weekday,
            'execution_time': self.execution_time.strftime('%H:%M:%S'),
            'test_suite_id': self.test_suite_id,
            'test_suite_name': self.test_suite.suite_name if self.test_suite else None,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

class TestExecution(db.Model):
    """测试执行记录"""
    id = db.Column(db.Integer, primary_key=True)
    strategy_id = db.Column(db.Integer, db.ForeignKey('test_strategies.id'))
    start_time = db.Column(db.DateTime)
    end_time = db.Column(db.DateTime)
    status = db.Column(db.String(20))  # running/completed/failed
    log_path = db.Column(db.String(200))
    performance_data = db.Column(db.JSON)


class MQTTConfig(db.Model):
    """MQTT配置表"""
    __tablename__ = 'mqtt_configs'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    config_name = db.Column(db.String(100), nullable=False, unique=True, comment='配置名称')
    broker = db.Column(db.String(200), nullable=False, comment='MQTT服务器地址')
    port = db.Column(db.Integer, nullable=False, default=8883, comment='端口号')
    client_id = db.Column(db.String(100), nullable=False, comment='客户端ID')
    username = db.Column(db.String(100), nullable=False, comment='用户名')
    password = db.Column(db.String(100), nullable=False, comment='密码')
    is_default = db.Column(db.Boolean, nullable=False, default=False, comment='是否为默认配置')
    description = db.Column(db.Text, nullable=True, comment='配置描述')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        """转换为字典格式"""
        return {
            'id': self.id,
            'config_name': self.config_name,
            'broker': self.broker,
            'port': self.port,
            'client_id': self.client_id,
            'username': self.username,
            'password': self.password,
            'is_default': self.is_default,
            'description': self.description,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

    @staticmethod
    def create_config(data):
        """创建新的MQTT配置"""
        try:
            # 如果设置为默认配置，先将其他配置设为非默认
            if data.get('is_default', False):
                MQTTConfig.query.update({'is_default': False})
            
            new_config = MQTTConfig(
                config_name=data['config_name'],
                broker=data['broker'],
                port=data.get('port', 8883),
                client_id=data['client_id'],
                username=data['username'],
                password=data['password'],
                is_default=data.get('is_default', False),
                description=data.get('description')
            )
            db.session.add(new_config)
            db.session.commit()
            return new_config
        except Exception as e:
            db.session.rollback()
            raise e

    def update_config(self, data):
        """更新MQTT配置"""
        try:
            # 如果设置为默认配置，先将其他配置设为非默认
            if data.get('is_default', False) and not self.is_default:
                MQTTConfig.query.filter(MQTTConfig.id != self.id).update({'is_default': False})
            
            for key, value in data.items():
                if hasattr(self, key) and key not in ['id', 'created_at']:
                    setattr(self, key, value)
            db.session.commit()
            return self
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def delete_config(config_id):
        """删除MQTT配置"""
        try:
            config = MQTTConfig.query.get(config_id)
            if not config:
                return False, "MQTT配置不存在"
            
            # 检查是否为默认配置
            if config.is_default:
                # 检查是否还有其他配置
                other_configs = MQTTConfig.query.filter(MQTTConfig.id != config_id).all()
                if other_configs:
                    # 如果有其他配置，设置第一个为默认配置
                    other_configs[0].is_default = True
                    db.session.add(other_configs[0])
                else:
                    # 如果没有其他配置，不允许删除最后一个默认配置
                    return False, "不能删除最后一个MQTT配置，系统至少需要保留一个配置"
            
            # 查询相关的升级记录数量（仅用于记录）
            record_count = UpgradeRecord.query.filter_by(config_id=config_id).count()
            
            db.session.delete(config)
            db.session.commit()
            
            message = f"删除成功"
            if record_count > 0:
                message += f"，同时删除了 {record_count} 条相关升级记录"
            
            return True, message
            
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def get_config_by_id(config_id):
        """根据ID获取MQTT配置"""
        return MQTTConfig.query.get(config_id)

    @staticmethod
    def get_default_config():
        """获取默认MQTT配置"""
        return MQTTConfig.query.filter_by(is_default=True).first()

    @staticmethod
    def get_all_configs():
        """获取所有MQTT配置"""
        return MQTTConfig.query.all()


class UpgradeRecord(db.Model):
    """升级记录表"""
    __tablename__ = 'upgrade_records'

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    config_id = db.Column(db.Integer, db.ForeignKey('mqtt_configs.id', ondelete='CASCADE'),
                         nullable=False, comment='使用的MQTT配置ID')
    device_names = db.Column(db.Text, nullable=False, comment='设备名称列表(JSON格式)')
    upgrade_message = db.Column(db.Text, nullable=False, comment='升级消息(JSON格式)')
    status = db.Column(db.String(20), nullable=False, default='PENDING', 
                      comment='升级状态: PENDING/SUCCESS/FAILED')
    error_message = db.Column(db.Text, nullable=True, comment='错误信息')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # 关联MQTT配置，设置CASCADE删除
    mqtt_config = db.relationship('MQTTConfig', backref=db.backref('upgrade_records', 
                                                                   lazy=True, 
                                                                   cascade='all, delete-orphan'))

    def to_dict(self):
        """转换为字典格式"""
        import json
        try:
            device_names = json.loads(self.device_names) if self.device_names else []
            upgrade_message = json.loads(self.upgrade_message) if self.upgrade_message else {}
        except:
            device_names = []
            upgrade_message = {}
            
        return {
            'id': self.id,
            'config_id': self.config_id,
            'config_name': self.mqtt_config.config_name if self.mqtt_config else None,
            'device_names': device_names,
            'upgrade_message': upgrade_message,
            'status': self.status,
            'error_message': self.error_message,
            'created_at': self.created_at.strftime('%Y-%m-%d %H:%M:%S'),
            'updated_at': self.updated_at.strftime('%Y-%m-%d %H:%M:%S')
        }

    @staticmethod
    def create_record(data):
        """创建升级记录"""
        try:
            import json
            new_record = UpgradeRecord(
                config_id=data['config_id'],
                device_names=json.dumps(data['device_names'], ensure_ascii=False),
                upgrade_message=json.dumps(data['upgrade_message'], ensure_ascii=False),
                status=data.get('status', 'PENDING'),
                error_message=data.get('error_message')
            )
            db.session.add(new_record)
            db.session.commit()
            return new_record
        except Exception as e:
            db.session.rollback()
            raise e

    def update_status(self, status, error_message=None):
        """更新升级状态"""
        try:
            self.status = status
            if error_message:
                self.error_message = error_message
            db.session.commit()
            return self
        except Exception as e:
            db.session.rollback()
            raise e


